package cn.e2win.biz.base;

import cn.e2win.biz.util.StringUtils;

public class CommonStatic {

	public static String labSql = "SELECT entityId,entityType, labelType, GROUP_CONCAT(label SEPARATOR ' | ') AS label FROM sys_label GROUP BY entityId,entityType, labelType";
	public static String guestRelationSql = "SELECT entityId,entityType, GROUP_CONCAT(empName SEPARATOR ' | ') AS guestNames FROM guest_relation GROUP BY entityId,entityType";
	public static String dsRelationSql = "SELECT t1.entityId,t1.entityType "
			+ ",GROUP_CONCAT(t2.empName SEPARATOR ' | ') AS dsEmpNames "
			+ ",CONCAT('|',GROUP_CONCAT(t1.dsEmpId SEPARATOR '|'),'|') AS dsEmpIds "
			+ " FROM ds_relation t1, ds_emp t2 "
			+ " WHERE t1.dsEmpId = t2.id"
			+ " GROUP BY t1.entityId,t1.entityType";
	
	public static String getDistanceSql(String x, String y, String xField, String yField, String distanceFieldM,String distanceFieldKm){
		String sql = ", null AS  " + distanceFieldM + ", null AS  " + distanceFieldKm;
		if(!StringUtils.isEmpty(x) && !StringUtils.isEmpty(y)){
			sql = " ,(st_distance (point ("+x+", "+y+"),point(" + xField + "," + yField + ") ) *111195) AS " + distanceFieldM + 
				  " ,CASE WHEN (st_distance (point ("+x+", "+y+"),point(" + xField + "," + yField + ") ) *111195)<1000 "
		  		+ " THEN CONCAT(ROUND(st_distance (point ("+x+", "+y+"),point(" + xField + "," + yField + ") ) *111195,0), '米') "
				+ " ELSE CONCAT(ROUND(st_distance (point ("+x+", "+y+"),point(" + xField + "," + yField + ") ) *111.195,1), '千米') "
				+ " END AS " + distanceFieldKm; 
				//111195=地球半径6371000*PI/180    是将值转化为米
		}

		return sql;
	}

}
